package cn.aias.rbac.domain.repository;

import cn.aias.rbac.domain.model.User;
import cn.aias.rbac.application.dto.UserQueryDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    @Query("SELECT u FROM User u LEFT JOIN FETCH u.roles WHERE " +
            "(:#{#query.username} IS NULL OR u.username LIKE %:#{#query.username}%) AND " +
            "(:#{#query.email} IS NULL OR u.email LIKE %:#{#query.email}%)")
    Page<User> findByPage(UserQueryDTO query, Pageable pageable);  // 分页查询


    // JPQL 中 ?1 对应 username，?2 对应 email，?3 和 ?4 可能重复（根据逻辑调整）
    @Query("SELECT u FROM User u LEFT JOIN FETCH u.roles WHERE " +
            "(?1 IS NULL OR u.username LIKE CONCAT('%', ?1, '%')) AND " +  // 合并为 1 个参数
            "(?2 IS NULL OR u.email LIKE CONCAT('%', ?2, '%'))")
    // 合并为 1 个参数
    Page<User> findByPage(String username, String email, Pageable pageable);

    // 原生 SQL 写法（H2 数据库）
    @Query(
            value = "SELECT u.* FROM users u " +
                    "LEFT JOIN user_roles ur ON u.id = ur.user_id " +
                    "LEFT JOIN roles r ON ur.role_id = r.id " +
                    "WHERE (:username IS NULL OR u.username LIKE CONCAT('%', :username, '%')) " +
                    "AND (:email IS NULL OR u.email LIKE CONCAT('%', :email, '%'))",
            countQuery = "SELECT COUNT(u.id) FROM users u " +  // 分页计数查询
                    "WHERE (:username IS NULL OR u.username LIKE CONCAT('%', :username, '%')) " +
                    "AND (:email IS NULL OR u.email LIKE CONCAT('%', :email, '%'))",
            nativeQuery = true
    )
    Page<User> findByPageNative(@Param("username") String username, @Param("email") String email, Pageable pageable);

}
